Create database WF1
Go
Use WF1
Go
Create table tbOrderBill
(
	BillNo varchar(5) primary key,
	Product varchar(30),
	Description varchar(500),
	Quantity int,
	UnitPrice int
)
Go
Insert into tbOrderBill values
('BN101','Laptop Dell','Intel Core i5 2500M,CPU 2.3MHZ, 4GB RAM, 540GB',2,850);
Insert into tbOrderBill values
('BN102','Laptop Vaio','Intel Core i7 3800M,CPU 3.3MHZ, 8GB RAM, 1TB',5,1500);
Insert into tbOrderBill values
('BN103','Laptop Hp','Intel Core i5 2100M,CPU 2.1MHZ, 2GB RAM, 440GB',1,688);
Insert into tbOrderBill values
('BN104','Laptop Asus','Intel Core i7 3100M,CPU 3.0MHZ, 8GB RAM, 640GB',3,1250);
Insert into tbOrderBill values
('BN105','Laptop Axioo','Intel Core i5 1800M,CPU 2.5MHZ, 4GB RAM, 240GB',1,650);
Insert into tbOrderBill values
('BN106','Laptop Acer','Intel Core i3 1200M,CPU 2.1MHZ, 2GB RAM, 340GB',2,350);
Insert into tbOrderBill values
('BN107','Laptop Apple','Intel Core i5 2400M,CPU 2.6MHZ, 4GB RAM, 240GB',4,875);
go
Update tbOrderBill
Set Product = '',Description ='', Quantity = 0 , UnitPrice = 0
Where BillNo = 'BN107'
Go
Delete tbOrderBill
Where BillNo = 'BN107'

Create table tbCustomer
(
	CustomerID Int identity primary key,
	CustName Varchar(50),
	CustAge int constraint CK_CustAge Check(CustAge > 18),
	CustAddress Varchar(100),
	CustEMail Varchar(50),
	Problem Varchar(256),
	Status Bit,
	Date DateTime,
	DateSolved DateTime,
)
go
Insert tbCustomer (CustName,CustAge,CustAddress,CustEMail,Problem,Status,Date,DateSolved)
values('Kaido',23,'89 Nguyen Tat Thanh','kaido@yahoo.com','Hardware damaged',1,'8/28/2012','9/5/2012');
Insert tbCustomer (CustName,CustAge,CustAddress,CustEMail,Problem,Status,Date,DateSolved)
values('Ginta',20,'19 Trieu Quang Phuc','ginta@yahoo.com','System error',0,'8/20/2012','9/15/2012');
Insert tbCustomer (CustName,CustAge,CustAddress,CustEMail,Problem,Status,Date,DateSolved)
values('Wally',23,'11 Hoang Dieu','wally@yahoo.com','Virus',0,'8/1/2012','9/25/2012');
Insert tbCustomer (CustName,CustAge,CustAddress,CustEMail,Problem,Status,Date,DateSolved)
values('Luffy',23,'24 Hoang Hoa Tham','luffy@yahoo.com','Hardware damaged',0,'7/28/2012','9/18/2012');
Insert tbCustomer (CustName,CustAge,CustAddress,CustEMail,Problem,Status,Date,DateSolved)
values('Robin',25,'59 Le Loi','robin@yahoo.com','System error',1,'8/10/2012','9/6/2012');
Insert tbCustomer (CustName,CustAge,CustAddress,CustEMail,Problem,Status,Date,DateSolved)
values('Vivi',19,'102 Nguyen Van Cu','vivi@yahoo.com','Hardware damaged',1,'8/1/2012','9/1/2012');
Insert tbCustomer (CustName,CustAge,CustAddress,CustEMail,Problem,Status,Date,DateSolved)
values('Tashi',21,'17 Pham Ngu Lao','tashi@yahoo.com','Virus',1,'6/28/2012','7/5/2012');
Insert tbCustomer (CustName,CustAge,CustAddress,CustEMail,Problem,Status,Date,DateSolved)
values('Smoker',28,'49 Nguyen Kiem','smoker@yahoo.com','Hardware damaged',0,'9/1/2012','10/5/2012');
Go
Create table tbPatient
(
	PatientID int primary key,
	PatientName Varchar(50),
	PatientAge int constraint CK_PatientAge Check(PatientAge > 18),
	PatientAddress Varchar(100),
	PatientCountry Varchar(50),
	DiagnoticTest Varchar(50),
	DateOfTesting DateTime,
	DateOfReport DateTime
)
Go
Insert tbPatient
values(1,'Tenten','19','78 Le Loi','VietNam','Urine','6/28/2012','7/5/2012');
Insert tbPatient
values(2,'Kuro','26','19 Tran Hung Dao','VietNam','Stool','7/11/2012','8/25/2012');
Insert tbPatient
values(3,'Kasim','21','27 Tran Tuong Cong','Thailan','Blood','8/16/2012','9/15/2012');
Insert tbPatient
values(4,'Eve','30','31 Thanh Thai','Singapore','Test','9/26/2012','10/1/2012');
Insert tbPatient
values(5,'Nano','25','43 Su Van Hanh','Thailan','Stool','7/14/2012','8/24/2012');
Insert tbPatient
values(6,'Susu','27','81 Lac Long Quan','Singapore','Urine','5/27/2012','7/18/2012');
Go
Create table tbEmployee
(
	EmpID Varchar(10) primary key,
	EmpName Varchar(20)not null,
	EmpPass Varchar(10)not null,
	isAdmin Bit default 0 not null
)
Go
Insert tbEmployee values('E01','Kaido','123',1);
Insert tbEmployee values('E02','Ginta','123',0);
Insert tbEmployee values('E03','Wally','123',0);
Insert tbEmployee values('E04','Wilson','123',0);
Insert tbEmployee values('E05','Edward','123',0);
Insert tbEmployee values('E06','Luci','123',0);
Insert tbEmployee values('E07','Erza','123',0);
Go
Create table tbProduct
(
	ProductID Varchar(10)primary key,
	ProductName Varchar(40),
	Price Int,
	Type Varchar(10)
)
Go
Insert tbProduct values('C101','FORD',5000,'Car');
Insert tbProduct values('B102','HONDA',1000,'Bikes');
Insert tbProduct values('C103','KIA',3509,'Car');
Insert tbProduct values('B104','YAMAHA',1100,'Bikes');
Insert tbProduct values('C105','FIAT',4500,'Car');
Insert tbProduct values('B106','SYM',900,'Bikes');
Insert tbProduct values('C107','BMW',6000,'Car');
Insert tbProduct values('B108','SUZUKI',950,'Bikes');
Insert tbProduct values('C109','TOYOTA',4800,'Car');
Go
Create table tbOrders
(
	OrderID Varchar(10) primary key,
	OrderDate DateTime,
	CustID Int foreign Key references tbCustomer(CustomerID),
	ProductID Varchar(10)foreign Key references tbProduct(ProductID),
	Quantity Int
)
Go
Insert tbOrders values('D01','9/9/2012',1,'C107',2);
Insert tbOrders values('D02','7/1/2012',2,'C101',2);
Insert tbOrders values('D03','8/2/2012',4,'C103',2);
Insert tbOrders values('D04','5/18/2012',7,'C105',2);
Insert tbOrders values('D05','4/15/2012',3,'C109',2);
Insert tbOrders values('D06','2/11/2012',5,'B102',2);
Insert tbOrders values('D07','1/21/2012',6,'B108',2);
Insert tbOrders values('D08','3/12/2012',8,'B106',2);
Go
Create table tbStudent
(
	StudentID Varchar(10)primary key,
	StudentName Varchar(20),
	StudentAdress Varchar(50),
	Marks float
)
Go
Insert tbStudent values('S01','Do Ho Xuan Luan','80 Nguyen Tat Thanh F13 Q4',81);
Insert tbStudent values('S02','Truong Minh Duc','19 Trieu Quang Phuc F10 Q5',78);
Insert tbStudent values('S03','Le Duy','15 Cach Mang Thang 8 F9 Q3',79);
Insert tbStudent values('S04','Tran Trung Kinh','76 Nguyen Thai Hoc F4 Q1',75);
Insert tbStudent values('S05','Pham Ngoc Hoa','25 Nguyen Kiem F10 QTB',71);
Insert tbStudent values('S06','Pham Xuan Viet','15 Truong Chinh F8 QTB',69);
Insert tbStudent values('S07','Ly Chieu Chieu','1 Duong Vanh Dai Trong F13 QBT',72);
Insert tbStudent values('S08','Lam Duc Tuan','9 3 Thang 2 F7 Q10',74);
Insert tbStudent values('S09','Trieu Tu Vy','85 Ly Thuong Kiet F10 Q10',65);
Insert tbStudent values('S10','Vo Van Viet','17 Hoang Dieu F8 Q4',50);
Go
Select * from tbStudent
Go
Select * from tbOrders
Go
Select * from tbProduct
go
Select * from tbOrderBill
Go
select * from tbCustomer
Go
Select * from tbPatient
Go
Select * from tbEmployee
Go
Create table PersonalInfo
(
	ID  int identity primary key,
	Name nvarchar(100)not null,
	Address nvarchar(200)not null,
	Email nvarchar(100)not null,
	URL nvarchar(100) ,
	HomePhone varchar(20),
	CellPhone varchar(20),
	WorkPhone varchar(20),
	Category nvarchar(50),
	DateOfBirth datetime,
	Remarks ntext
)
Go
Insert PersonalInfo (Name,Address,Email,URL,HomePhone,CellPhone,WorkPhone,Category,DateOfBirth,Remarks)
values ('Kaido','69 Nguyen Tat Thanh F13 Q4','kaido@yahoo.com','facebook/kaido','38255525','0907388204','0983191089','Business','11/20/1989','Deluty General Manager');
Insert PersonalInfo (Name,Address,Email,URL,HomePhone,CellPhone,WorkPhone,Category,DateOfBirth,Remarks)
values ('Wilson','88 Trieu Tuong Cong F10 Q5','wilson@yahoo.com','facebook/wilson','38212325','0907876204','0945691089','Familly','10/19/1989','Deluty General Manager');
	
